package com.css.fxfzdzzh.modules.basicData.landslideSite.repository.impl;

import cn.hutool.core.date.DateUtil;
import com.alibaba.druid.sql.visitor.functions.If;
import com.alibaba.fastjson.JSONObject;
import com.css.fxfzdzzh.constants.FxfzConstants;
import com.css.fxfzdzzh.constants.YNEnum;
import com.css.fxfzdzzh.modules.basicData.landslideSite.entity.LandslideSiteFileData;
import com.css.fxfzdzzh.modules.basicData.landslideSite.entity.LandslideSiteMetaData;
import com.css.fxfzdzzh.modules.basicData.landslideSite.entity.LandslideSiteVersion;
import com.css.fxfzdzzh.modules.basicData.landslideSite.entity.param.DataVersionPageParam;
import com.css.fxfzdzzh.modules.basicData.landslideSite.repository.LandslideSiteRepository;
import com.css.fxfzdzzh.modules.basicData.landslideSite.vo.DataPageVO;
import com.css.fxfzdzzh.modules.basicData.param.SysDataVersionSameTypeDataExistParam;
import com.css.fxfzdzzh.modules.eqLandslide.lawAnalysis.vo.SlopesVO;
import com.css.fxfzdzzh.modules.evaluate.VO.VersionPageVO;
import com.css.fxfzdzzh.modules.tifData.service.ElevationMapService;
import com.css.fxfzdzzh.util.CreateGson;
import com.css.fxfzdzzh.util.EasyPoiExcelUtil;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.web.PlatformSessionContext;
import com.css.fxfzdzzh.zcpt.sys.entity.SUser;
import com.css.fxfzdzzh.zcpt.sys.service.SUserService;
import com.css.fxfzdzzh.zcpt.sys.service.SysRoleService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年02月22日  13:57
 */
@Repository
public class LandslideSiteRepositoryImpl implements LandslideSiteRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    SysRoleService sysRoleService;

    @Resource
    SUserService sUserService;

    @Resource
    ElevationMapService elevationMapService;

    @Override
    public Map<String, Object> page(DataVersionPageParam dataVersionPageParam) {
        String userId = PlatformSessionContext.getUserID();
        StringBuilder str = new StringBuilder("");
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String provinceId = sUser.getProvinceId();
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        String roles = str.toString();

        StringBuilder sql = new StringBuilder("select * from dzzh_data_version");
        StringBuilder whereSql = new StringBuilder(" where  1=1 and del_flag='"+YNEnum.N.toString()+"'");
        //国家级的用户: 查看所有数据
        //省级用户：国家级-全国、国家级-本省、省级-本省
//        if (!roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE) && !roles.contains(FxfzConstants.DZZH_COUN_YW_ROLE)){
//            whereSql = whereSql.append(" and (data_range_province is null or data_range_province = '").append(provinceId).append("')");
//        }
        StringBuilder countSql = new StringBuilder(" select count(*) from dzzh_data_version");
        int firstIndex = (dataVersionPageParam.getPage() - 1) * dataVersionPageParam.getRows();
        int lastIndex = dataVersionPageParam.getRows();
        pageSqlBuilder(dataVersionPageParam,whereSql);
        String sqlWhole = sql.append(whereSql).append(" order by create_time desc").append(" limit ").append(lastIndex).append(" offset ").append(firstIndex).toString();
        countSql.append(whereSql);
        List<LandslideSiteVersion> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(LandslideSiteVersion.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    private void pageSqlBuilder(DataVersionPageParam param, StringBuilder sqlBuilder) {
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        if (param.getVersionStartTime() != null && param.getVersionEndTime() != null) {
            sqlBuilder.append(" and date(version_time) between '").append(DateUtil.format(param.getVersionStartTime(), "yyyy-MM-dd")).append("' and '").append(DateUtil.format(param.getVersionEndTime(), "yyyy-MM-dd")).append("' ");
        }
        if (param.getUploadStartTime() != null && param.getUploadEndTime() != null) {
            sqlBuilder.append(" and date(upload_time) between '").append(DateUtil.format(param.getUploadStartTime(), "yyyy-MM-dd")).append("' and '").append(DateUtil.format(param.getUploadEndTime(), "yyyy-MM-dd")).append("' ");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getVersionCode())) {
            sqlBuilder.append(" and version_code like '%").append(param.getVersionCode().trim()).append("%' ");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataName())) {
            sqlBuilder.append(" and data_name like '%").append(param.getDataName().trim()).append("%' ");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataRangeCountry())) {
            sqlBuilder.append(" and data_range_country = '").append(param.getDataRangeCountry()).append("' ");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataRangeProvince())) {
            sqlBuilder.append(" and data_range_province = '").append(param.getDataRangeProvince()).append("' ");
        }
        //如果没查询条件默认查国家级和用户所在省的数据
//        if (PlatformObjectUtils.isEmpty(param.getDataRangeCountry()) && PlatformObjectUtils.isEmpty(param.getDataRangeProvince())) {
//            sqlBuilder.append(" and (data_range_country = '").append(param.getDataRangeCountry()).append("' or  ");
//            sqlBuilder.append(" data_range_province = '").append(sUser.getProvinceId()).append("') ");
//        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataRangeProvince())) {
            sqlBuilder.append(" and data_range_province = '").append(param.getDataRangeProvince()).append("' ");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataRangeCity())) {
            sqlBuilder.append(" and data_range_city = '").append(param.getDataRangeCity()).append("' ");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataRangeArea())) {
            sqlBuilder.append(" and data_range_area = '").append(param.getDataRangeArea()).append("' ");
        }
        String role = sysRoleService.getRoleByUserId(PlatformSessionContext.getUserID());
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        StringBuilder str=new StringBuilder("");
        for(Map roleMap:roleMaps){
            str.append(roleMap.get("roleCode")+",");
        }
        String roles = str.toString();
        if(roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE) || roles.contains(FxfzConstants.DZZH_COUN_YW_ROLE)){
            //国家级管理员和国家级业务人员可以看国家级数据
           //sqlBuilder.append(" and data_range_country = '1'");
        }else if(roles.contains(FxfzConstants.DZZH_PRI_GL_ROLE) || roles.contains(FxfzConstants.DZZH_PRI_YW_ROLE)){
          // 省级管理员和省级业务人员可以全看自己省和国家级的
//            sqlBuilder.append(
//              " and (data_range_country = '1' or data_range_province = '"+sUser.getProvinceId()+"')");
            sqlBuilder.append(
                    " and (( data_range_country = '2' and data_range_province = '"+sUser.getProvinceId()+"' ) " +
                            "or ( data_range_country = '1' and data_range_province = '"+sUser.getProvinceId()+"' )" +
                            "or ( data_range_country = '1' and data_range_province = '' ) \n" +
                            "or ( data_range_country = '1' and data_range_province is null ))");
        }
    }

    @Override
    public void save(LandslideSiteVersion landslideSite) {
        String sql =
            "insert into dzzh_data_version("+
        "ddv_id,version_code, " +
                "version_time, " +
                "version_desc, " +
                "upload_user, " +
                "update_desc, " +
                "data_range_country, " +
                "data_range_province," +
                "data_range_city," +
                "data_name," +
                "data_desc," +
                "upload_time," +
                "data_file_ids," +
                "attachment_ids, " +
                "data_range_area, " +
                "data_range_str," +
                "create_time," +
                "create_user,upload_user_name,del_flag)" +
                " values(" +
                    ":ddvId, " +
                    ":versionCode, " +
                    ":versionTime, " +
                    ":versionDesc, " +
                    ":uploadUser, " +
                    ":updateDesc, " +
                    ":dataRangeCountry, " +
                    ":dataRangeProvince," +
                    ":dataRangeCity," +
                    ":dataName," +
                    ":dataDesc," +
                    ":uploadTime," +
                    ":dataFileIds," +
                    ":attachmentIds, " +
                    ":dataRangeArea, " +
                    ":dataRangeStr," +
                    ":createTime," +
                    ":createUser,:uploadUserName,:delFlag" +
                    " )";
        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(landslideSite);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void delVersionDataByVersionCode(String versionCode,String replaceUser) {
    jdbcTemplate.update(
        "update dzzh_data_version  set del_flag='"
            + YNEnum.Y.toString()
            + "',update_user='"
            + replaceUser
            + "',update_time=now() where version_code = '"
            + versionCode
            + "' and del_flag='"+YNEnum.Y.toString()+"'");
    }

    @Override
    public LandslideSiteVersion getVersionInfo(String versionCode) {
        String sql = "select * from dzzh_data_version where version_code='"+versionCode+"' and del_flag='0'";
        List<LandslideSiteVersion> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LandslideSiteVersion.class));
        return list.size()>0?list.get(0):new LandslideSiteVersion();
    }

//    @Override
//    public void saveMetaDataInfo(LandslideSiteFileData metaData) {
//        String sql =
//                "insert into dzzh_version_meta_data values "
//                        + " values(" +
//                        ":dvmdId, " +
//                        ":ddvCode, " +
//                        ":name, " +
//                        ":outdoorNum, " +
//                        ":landslideTime, " +
//                        ":landslideYear, " +
//                        ":landslideMonth, " +
//                        ":landslideDay," +
//                        ":landslideType," +
//                        ":landslideShape," +
//                        ":crown," +
//                        ":toe," +
//                        ":longitude," +
//                        ":latitude," +
//                        ":town, " +
//                        ":village, " +
//                        ":underAge," +
//                        ":formationLithology," +
//                        ":structuralPosition," +
//                        ":earthquakeIntensity," +
//                        ":stratigraphicDip" +
//                        ":stratigraphicAngle" +
//                        ":microrelief" +
//                        ":relativeRiverPosition" +
//                        ":slopeStructureType" +
//                        ":controlStructureType" +
//                        ":controlStructureInclination" +
//                        ":controlStructureAngle" +
//                        ":landslideLength" +
//                        ":landslideWidth" +
//                        ":landslideThickness" +
//                        ":landslideSlope" +
//                        ":landslideAspect" +
//                        ":landslideArea" +
//                        ":landslideVolume" +
//                        ":landslidePlaneShape" +
//                        ":landslideProfileShape" +
//                        ":scaleGrade" +
//                        ":createTime" +
//                        ":createUser" +
//                        ":createUserName" +
//                        " )";
//        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(metaData);
//        namedParameterJdbcTemplate.update(sql, source);
//    }

    @Override
    public LandslideSiteMetaData getVersionMetaDataInfo(String versionCode) {
        String sql = "select * from dzzh_version_meta_data where version_code = '"+versionCode+"' and del_flag='"+YNEnum.N.toString()+"'";
        List<LandslideSiteMetaData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LandslideSiteMetaData.class));
        return list.size()>0?list.get(0):new LandslideSiteMetaData();
    }

    @Override
    public void saveMetaDataInfo(LandslideSiteMetaData metaData) {
        String sql =
        "insert into dzzh_version_meta_data(dvmd_id," +
                "version_code," +
                "data_source," +
                "data_range_country," +
                "data_range_province," +
                "data_range_city," +
                "data_range_area," +
                "is_spatial_data," +
                "spatial_data_type," +
                "projection_info," +
                "field_num," +
                "field_entry," +
                "upload_user," +
                "upload_user_name," +
                "upload_time," +
                "del_flag)" +
                " values(" +
                ":dvmdId, " +
                ":versionCode, " +
                ":dataSource, " +
                ":dataRangeCountry, " +
                ":dataRangeProvince, " +
                ":dataRangeCity, " +
                ":dataRangeArea, " +
                ":isSpatialData," +
                ":spatialDataType," +
                ":projectionInfo," +
                ":fieldNum," +
                ":fieldEntry," +
                ":uploadUser," +
                ":uploadUserName," +
                ":uploadTime," +
                ":delFlag" +
                " )";
        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(metaData);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateMetaDataInfo(LandslideSiteMetaData metaData) {
        String sql = "update dzzh_version_meta_data set spatial_data_type='"+metaData.getSpatialDataType()+"',projection_info='"+metaData.getProjectionInfo()+"'," +
                "field_num='"+metaData.getFieldNum()+"',field_entry='"+metaData.getFieldEntry()+"',update_user='"+metaData.getUpdateUser()+"',update_time=now() where dvmd_id='"+metaData.getDvmdId()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteMetaData(String versionCode,String updateUser) {
        String sql = "update dzzh_version_meta_data set del_flag='"+YNEnum.Y.toString()+"'," +
                "update_user='"+updateUser+"',update_time=now() where version_code='"+versionCode+"' and del_flag='"+YNEnum.N.toString()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteVersionData(String versionCode,String updateUser) {
    String sql =
        "update  dzzh_data_version  set del_flag='"
            + YNEnum.Y.toString()
            + "',update_user='"
            + updateUser
            + "',update_time=now() where version_code='"
            + versionCode
            + "' and del_flag='"+YNEnum.N.toString()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void batchSave(List<LandslideSiteFileData> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql =
        "insert into dzzh_version_file_data(dvmd_id,"
            + "version_code,"
            + "name,"
            + "outdoor_num,"
            + "landslide_time,"
            + "landslide_year,"
            + "landslide_month,"
            + "landslide_day,"
            + "landslide_type,"
            + "landslide_shape,"
            + "crown,"
            + "toe,"
            + "longitude,"
            + "latitude,"
            + "town,"
            + "village,"
            + "under_age,"
            + "formation_lithology,"
            + "structural_position,"
            + "earthquake_intensity,"
            + "stratigraphic_dip,"
            + "stratigraphic_angle,"
            + "microrelief,"
            + "relative_river_position,"
            + "slope_structure_type,"
            + "control_structure_type,"
            + "control_structure_inclination,"
            + "control_structure_angle,"
            + "landslide_length,"
            + "landslide_width,"
            + "landslide_thickness,"
            + "landslide_slope,"
            + "landslide_aspect,"
            + "landslide_area,"
            + "landslide_volume,"
            + "landslide_plane_shape,"
            + "landslide_profile_shape,"
            + "scale_grade,"
            + "create_time,"
            + "create_user,"
            + "create_user_name,"
            + "province,"
            + "city,"
            + "country,"
            + "del_flag,geom) "
            + " values("
            + ":dvmdId, "
            + ":versionCode, "
            + ":name, "
            + ":outdoorNum, "
            + ":landslideTime, "
            + ":landslideYear, "
            + ":landslideMonth, "
            + ":landslideDay,"
            + ":landslideType,"
            + ":landslideShape,"
            + ":crown,"
            + ":toe,"
            + ":longitude,"
            + ":latitude,"
            + ":town, "
            + ":village, "
            + ":underAge,"
            + ":formationLithology,"
            + ":structuralPosition,"
            + ":earthquakeIntensity,"
            + ":stratigraphicDip,"
            + ":stratigraphicAngle,"
            + ":microrelief,"
            + ":relativeRiverPosition,"
            + ":slopeStructureType,"
            + ":controlStructureType,"
            + ":controlStructureInclination,"
            + ":controlStructureAngle,"
            + ":landslideLength,"
            + ":landslideWidth,"
            + ":landslideThickness,"
            + ":landslideSlope,"
            + ":landslideAspect,"
            + ":landslideArea,"
            + ":landslideVolume,"
            + ":landslidePlaneShape,"
            + ":landslideProfileShape,"
            + ":scaleGrade,"
            + ":createTime,"
            + ":createUser,"
            + ":createUserName,"
            + ":province,"
            + ":city,"
            + ":country,"
            + ":delFlag,ST_GeometryFromText(:geom,4490)"
            + " )";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public Map<String, Object> dataPage(DataPageVO dataPageVO) {
        StringBuilder sql = new StringBuilder("select * from dzzh_version_file_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString() +"'");
        StringBuilder countSql = new StringBuilder(" select count(*) from dzzh_version_file_data");
        int firstIndex = (dataPageVO.getPage() - 1) * dataPageVO.getRows();
        int lastIndex = dataPageVO.getRows();
        dataPageSqlBuilder(dataPageVO,whereSql);
        String sqlWhole = sql.append(whereSql).append(" order by create_time desc").append(" limit ").append(lastIndex).append(" offset ").append(firstIndex).toString();
        countSql.append(whereSql);
        List<LandslideSiteFileData> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(LandslideSiteFileData.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    private void dataPageSqlBuilder(DataPageVO param, StringBuilder sqlBuilder) {
        if (StringUtils.isNotBlank(param.getName())) {
            sqlBuilder.append(" and name like '%").append(param.getName()).append("%'");
        }
        if (StringUtils.isNotBlank(param.getLandslideTime())) {
            sqlBuilder.append(" and landslide_time = '").append(param.getLandslideTime()).append("'");
        }
        if (StringUtils.isNotBlank(param.getLandslideShape())) {
            sqlBuilder.append(" and landslide_shape ='").append(param.getLandslideShape()).append("'");
        }
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(PlatformSessionContext.getUserID());
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        StringBuilder str=new StringBuilder("");
        for(Map roleMap:roleMaps){
            str.append(roleMap.get("roleCode")+",");
        }
        String roles = str.toString();
        if (StringUtils.isNotBlank(param.getProvince())) {
            sqlBuilder.append(" and province ='").append(param.getProvince()).append("'");
        }else {
            if(roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE) || roles.contains(FxfzConstants.DZZH_COUN_YW_ROLE)){
                //国家级管理员和国家级业务人员可以全看全部
            }else if(roles.contains(FxfzConstants.DZZH_PRI_GL_ROLE) || roles.contains(FxfzConstants.DZZH_PRI_YW_ROLE)){
                //省级管理员和省级业务人员可以全看自己
                sqlBuilder.append(" and province = '"+sUser.getProvinceId()+"'");
            }
        }
        if (StringUtils.isNotBlank(param.getCity())) {
            sqlBuilder.append(" and city ='").append(param.getCity()).append("'");
        }
        if (StringUtils.isNotBlank(param.getCountry())) {
            sqlBuilder.append(" and country ='").append(param.getCountry()).append("'");
        }
        if (StringUtils.isNotBlank(param.getTown())) {
            sqlBuilder.append(" and town like '%").append(param.getTown()).append("%'");
        }
        if (StringUtils.isNotBlank(param.getVillage())) {
            sqlBuilder.append(" and village like'%").append(param.getVillage()).append("%'");
        }
        if (StringUtils.isNotBlank(param.getLandslideType())) {
            sqlBuilder.append(" and landslide_type ='").append(param.getLandslideType()).append("'");
        }
        if (StringUtils.isNotBlank(param.getVersionCode())) {
            sqlBuilder.append(" and version_code ='").append(param.getVersionCode()).append("'");
        }
    }

    @Override
    public List<LandslideSiteFileData> getLanslideSiteFileData(DataPageVO dataPageVO) {
        StringBuilder sql = new StringBuilder("select * from dzzh_version_file_data ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString() +"'");
        dataPageSqlBuilder(dataPageVO,whereSql);
        String sqlWhole = sql.append(whereSql).append(" order by create_time desc").toString();
        List<LandslideSiteFileData> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(LandslideSiteFileData.class));
        return list;
    }

    @Override
    public void exportExcel(List<LandslideSiteFileData> list, HttpServletResponse response) {
        EasyPoiExcelUtil.exportExcel(list, "滑坡现场调查数据", "滑坡现场调查数据", LandslideSiteFileData.class, "滑坡现场调查数据.xlsx", response);
    }

    @Override
    public void deleteData(String versionCode, String userId) {
        String sql = "update dzzh_version_file_data set del_flag='"+YNEnum.Y.toString()+"'," +
                "update_user='123',update_time=now() where version_code='"+versionCode+"' and del_flag='"+YNEnum.N.toString()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public Map<String, Object> pageForAnalyse(VersionPageVO param) {
        StringBuilder sql = new StringBuilder("select * from dzzh_data_version");
        StringBuilder whereSql = new StringBuilder(" where  del_flag='0'");
        int firstIndex = (param.getPage() - 1) * param.getRows();
        int lastIndex = param.getRows();
        analysePageSqlBuilder(param,whereSql);
        String sqlWhole = sql.append(whereSql).append(" order by create_time desc").append(" limit ").append(lastIndex).append(" offset ").append(firstIndex).toString();
        List<LandslideSiteVersion> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(LandslideSiteVersion.class));
        Integer total = jdbcTemplate.queryForObject(" select count(*) from dzzh_data_version" + whereSql, Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<SlopesVO> getSlopeAreaByGeomAndVersion1(String geomText, String param2) {
        String sql = "select x.longitude longitude,x.latitude latitude,x.landslide_area landslideArea,x.dvmd_id id from dzzh_version_file_data x " +
                "where st_intersects(st_geometryfromtext('POINT(' || x.longitude || ' ' || x.latitude || ')',4490)" +
                ",st_geomfromtext(?,4490)) and version_code=? ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SlopesVO.class), geomText,param2);
    }
    @Override
    public List<Map<String,Object>> getByGeomAndVersion2(String tableName, String param2) {
        //转换默认4490坐标系
        String sql0 = "SELECT UpdateGeometrySRID('"+tableName+"','smgeometry',4490)";
        jdbcTemplate.query(sql0, new BeanPropertyRowMapper<>(String.class));
        //查询相交数据
        String sql = "select b.gridvalue gridvalue,b.smarea smarea,sum(a.landslide_area) sum,st_AsText(b.smgeometry) smgeometry" +
                " from dzzh_version_file_data a,"+tableName+" b" +
                " where a.version_code='"+param2+"' and st_intersects(a.geom, b.smgeometry) GROUP BY b" +
                ".gridvalue, b.smarea,b.smgeometry";
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }
    private void analysePageSqlBuilder(VersionPageVO param, StringBuilder sqlBuilder) {
        if (param.getUploadStartTime() != null && param.getUploadEndTime() != null) {
            sqlBuilder.append(" and upload_time between '").append(DateUtil.format(param.getUploadStartTime(), "yyyy-MM-dd")).append("' and '").append(DateUtil.format(param.getUploadEndTime(), "yyyy-MM-dd")).append("' ");
        }
        if (StringUtils.isNotBlank(param.getVersionDesc())) {
            sqlBuilder.append(" and version_desc like '%").append(param.getVersionDesc().trim()).append("%' ");
        }
        if (StringUtils.isNotBlank(param.getUploadUser())) {
            sqlBuilder.append(" and upload_user_name like '%").append(param.getUploadUser().trim()).append("%' ");
        }
        if(StringUtils.isNotBlank(param.getProvince())){
            //国家级数据和省级（本省的）
            sqlBuilder.append(" and (data_range_country = '1' or (data_range_province = '").append(param.getProvince()).append("' and data_range_country = '2'))");
        }else{
            sqlBuilder.append(" and (data_range_country = '1')");
        }

    }

    @Override
    public String sameTypeDataExist(SysDataVersionSameTypeDataExistParam sysDataVersionSameTypeDataExistParam) {
        StringBuilder sqlBuilder = new StringBuilder("select version_code from dzzh_data_version where  del_flag='0' ");
        sqlBuilder.append(" and data_range_country = ").append(sysDataVersionSameTypeDataExistParam.getDataRangeCountry());
        if (StringUtils.isNotBlank(sysDataVersionSameTypeDataExistParam.getDataRangeProvince())) {
            sqlBuilder.append(" and data_range_province = '").append(sysDataVersionSameTypeDataExistParam.getDataRangeProvince()).append("'");
        }
        if (StringUtils.isNotBlank(sysDataVersionSameTypeDataExistParam.getDataRangeCity())) {
            sqlBuilder.append(" and data_range_city = '").append(sysDataVersionSameTypeDataExistParam.getDataRangeCity()).append("'");
        }
        if (StringUtils.isNotBlank(sysDataVersionSameTypeDataExistParam.getDataRangeArea())) {
            sqlBuilder.append(" and data_range_area = '").append(sysDataVersionSameTypeDataExistParam.getDataRangeArea()).append("'");
        }
        try {
            List<String> list = jdbcTemplate.queryForList(sqlBuilder.toString(), String.class);
            return PlatformObjectUtils.isNotEmpty(list) && list.size()>0?list.get(0):"";
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<String> getVersionCodeListByType() {
        String sql = "select version_code from dzzh_data_version where  del_flag='0'";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public void delOldVersionData(String versionCode) {
        String sql = "update dzzh_version_file_data set del_flag='1',update_user='"+PlatformSessionContext.getUserID()+"',update_time=now() where version_code='"+versionCode+"' and del_flag='2'";
        jdbcTemplate.update(sql);
    }

    @Override
    public Integer getUploadFileNum(String versionCode) {
        String sql = "select count(*) from dzzh_version_file_data where version_code='"+versionCode+"' and del_flag='0'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public void updateData(String versionCode) {
        String sql = "update dzzh_version_file_data set del_flag='2',update_user='"+PlatformSessionContext.getUserID()+"',update_time=now() where version_code='"+versionCode+"' and del_flag='0'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteFileData(String versionCode) {
        String sql = "delete from dzzh_version_file_data where version_code='"+versionCode+"' and del_flag='0'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void restoreFileData(String versionCode) {
    String sql =
        "update dzzh_version_file_data set del_flag='0',update_user='"+PlatformSessionContext.getUserID()+"',update_time=now() where version_code='"+versionCode+"' and del_flag='2'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void timingDeleteFileData(Date beginOfDay, Date endOfDay) {
        String sql =
        "update dzzh_version_file_data set del_flag='1',update_time=now(),update_user='系统管理员' where version_code in (select distinct version_code FROM dzzh_version_file_data where version_code not in (select  version_code from dzzh_data_version where del_flag='0' and create_time between '"+beginOfDay+"' and '"+endOfDay+"') "
            + " and create_time between '"
            + beginOfDay
            + "' and '"
            + endOfDay
            + "' and del_flag='0') ";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<LandslideSiteFileData> listForDp() {
        return jdbcTemplate.query("select * from dzzh_version_file_data where del_flag = '" + YNEnum.N.toString() + "'", new BeanPropertyRowMapper<>(LandslideSiteFileData.class));
    }
}

